from datetime import datetime

from django.core.exceptions import ObjectDoesNotExist
from django.db.models import F, Q

from oa.models import ProductBatch, MaterialOut, PackBatch, GroupBatch, PackBatchItem, \
    choices_product_state, choices_process_type, EquipmentCurrentProductBatch, FermentMonitor, ProcessRecord, \
    choices_tank_state
from tb3.utils import date_value, num_value, list_export_excel, convert_choices_to_dict


def product_batch_basic_data(product_batch, date_before=None):
    """
    生产批次的基本信息
    :param product_batch: 生产批次对象
    :param date_before: 截止日期
    :return: 返回生产批次的基础信息 data 和 生产过程中的原料使用 material_batches
    """
    data = dict()
    # 更新实际出酒率
    if product_batch.est_volume:
        est_volume = int(product_batch.est_volume)
    else:
        est_volume = 1750
    act_volume = product_batch.calculate_act_volume(date_before)
    # 如果没有 date_before 的话，更新 act_volume
    if not date_before:
        if not act_volume == product_batch.calculate_act_volume():
            product_batch.act_volume = act_volume
            product_batch.save()
    data['pk'] = product_batch.pk
    data['start_date'] = date_value(product_batch.start_date)
    data['finish_date'] = date_value(product_batch.finish_date)
    data['product_state'] = convert_choices_to_dict(choices_product_state).get(product_batch.product_state)
    data['product_name'] = product_batch.product_name.name
    data['current_tank'] = product_batch.tank_info
    data['batch_code'] = product_batch.code
    data['total_volume'] = product_batch.total_volume
    data['est_volume'] = est_volume
    data['act_volume'] = act_volume
    data['total_processing_costs'] = product_batch.total_processing_costs
    # 获取原料信息， 排除掉灌装过程中的原料
    material_batches = MaterialOut.objects.filter(
        related_order__product_batch=product_batch
    ).exclude(
        related_order__pack_batch__isnull=False
    )
    if date_before:
        material_batches = material_batches.filter(
            related_order__record_date__lte=date_before
        )
    return data, material_batches


def pack_amount_info(pack_batch):
    pack_amount = pack_batch['pack_amount']
    pack_unit = pack_batch['package_unit__unit']
    per_pack_amount = pack_batch['package_unit__amount']
    if not pack_unit == 'L':
        liter = float(per_pack_amount) * int(pack_amount) * 0.001
    else:
        liter = float(per_pack_amount) * int(pack_amount)
    return pack_amount, pack_unit, per_pack_amount, liter


def material_info(
        liter, est_material_cost_per_liter, act_material_cost_per_liter, date_before, product_batch, pack_batch):
    est_material_cost = round(liter * est_material_cost_per_liter, 4)
    act_material_cost = round(liter * act_material_cost_per_liter, 4)
    # # 计算包装成本
    material_batches = MaterialOut.objects.filter(
        related_order__product_batch=product_batch,
        related_order__pack_batch_id=pack_batch['pk']
    )
    if date_before:
        material_batches = material_batches.filter(related_order__record_date__lte=date_before)
    material_batches = material_batches.annotate(
        material_batch_code=F('material_batch__code'),
        material_code=F('material_batch__material__code'),
        material_name=F('material_batch__material__name'),
        material_batch_unit_cost=F('material_batch__unit_cost'),
    ).values(
        'id',
        'amount',
        'material_batch_code',
        'material_code',
        'material_name',
        'material_batch_unit_cost',
    )
    return est_material_cost, act_material_cost, material_batches


def pack_cost_info(pack_amount, pack_cost, est_material_cost, act_material_cost):
    if pack_amount:
        unit_pack_cost = round(pack_cost / pack_amount, 4)
        est_per_pack_cost = round((pack_cost + est_material_cost) / pack_amount, 4)
        act_per_pack_cost = round((pack_cost + act_material_cost) / pack_amount, 4)
    else:
        unit_pack_cost = 0
        est_per_pack_cost = 0
        act_per_pack_cost = 0
    est_total_cost = round(pack_cost + est_material_cost, 4)
    act_total_cost = round(pack_cost + act_material_cost, 4)
    return unit_pack_cost, est_per_pack_cost, act_per_pack_cost, est_total_cost, act_total_cost


def calculate_product_batch_cost(product_batch_id, date_before=None):
    """
    计算产品的成本
    :param product_batch_id: 生产批次的 ID
    :param date_before: 截止日期
    :return:
    """
    total_material_costs = 0
    try:
        product_batch = ProductBatch.objects.get(pk=product_batch_id)
    except ObjectDoesNotExist:
        return None
    batch_data, material_batches = product_batch_basic_data(product_batch, date_before=date_before)
    est_volume = batch_data['est_volume']
    act_volume = batch_data['act_volume']
    # 获取原料
    material_batches = material_batches.values(
        'id',
        'related_order__code',
        'related_order__record_date',
        'amount',
        'material_batch__unit',
        'material_batch__code',
        'material_batch__material__code',
        'material_batch__material__name',
        'material_batch__unit_cost',
    ).order_by('related_order__record_date', 'related_order__code')
    batch_data['materials'] = list()
    for material_batch in material_batches:
        unit_cost = num_value(material_batch['material_batch__unit_cost'], 4, True)
        batch_data['materials'].append(
            {
                'date': material_batch['related_order__record_date'],
                'order_no': material_batch['related_order__code'],
                'amount': float(material_batch['amount']),
                'unit': material_batch['material_batch__unit'],
                'batch_code': material_batch['material_batch__code'],
                'material_code': material_batch['material_batch__material__code'],
                'material_name': material_batch['material_batch__material__name'],
                'unit_cost': unit_cost,
                'cost': round(unit_cost * float(material_batch['amount']), 4),
            }
        )
        if material_batch['amount'] and unit_cost:
            total_material_costs += float(material_batch['amount']) * unit_cost
    # 计算生产阶段每升的成本，不包含包装费用
    total_production_cost = total_material_costs + batch_data['total_processing_costs']
    if est_volume:
        est_material_cost_per_liter = round(total_production_cost / est_volume, 4)
    else:
        est_material_cost_per_liter = 0
    if act_volume:
        act_material_cost_per_liter = round(total_production_cost / act_volume, 4)
    else:
        act_material_cost_per_liter = 0
    # 出酒率
    if product_batch.total_volume:
        est_product_ratio = est_volume / int(product_batch.total_volume) * 100
    else:
        est_product_ratio = 0
    if product_batch.total_volume:
        act_product_ratio = act_volume / int(product_batch.total_volume) * 100
    else:
        act_product_ratio = 0
    # 写入数据
    batch_data['total_material_costs'] = round(total_material_costs, 4)
    batch_data['total_production_cost'] = round(total_production_cost, 4)
    batch_data['est_material_cost_per_liter'] = est_material_cost_per_liter
    batch_data['act_material_cost_per_liter'] = act_material_cost_per_liter
    batch_data['est_product_ratio'] = est_product_ratio
    batch_data['act_product_ratio'] = act_product_ratio
    batch_data['total_pack_costs'] = 0
    # 灌装统计
    pack_batches = PackBatch.objects.filter(product_batch=product_batch)
    if date_before:
        pack_batches = pack_batches.filter(pack_start__lte=date_before)
    pack_batches = pack_batches.values(
        'pk',
        'code',
        'package_unit__display',
        'package_unit__amount',
        'package_unit__unit',
        'product_package__name',
        'pack_amount',
        'pack_start',
        'pack_end'
    ).distinct().order_by('pack_start')
    pack_batches_data = dict()
    batch_data['packs'] = list()
    for pack_batch in pack_batches:
        # 统计每个灌装批次的容量
        pack_amount, pack_unit, per_pack_amount, liter = pack_amount_info(pack_batch)
        est_material_cost, act_material_cost, material_batches = material_info(
            liter, est_material_cost_per_liter, act_material_cost_per_liter, date_before, product_batch, pack_batch)
        material_batches_list = list()
        pack_cost = 0
        for mb in material_batches:
            mbd = dict()
            for k, v in mb.items():
                mbd[k] = v
            if mb['material_batch_unit_cost'] and mb['amount']:
                pack_cost += float(mb['amount']) * float(mb['material_batch_unit_cost'])
                batch_data['total_pack_costs'] += float(mb['amount']) * float(mb['material_batch_unit_cost'])
                mbd['material_batch_cost'] = float(mb['amount']) * float(mb['material_batch_unit_cost'])
            else:
                mbd['material_batch_cost'] = 0
            material_batches_list.append(mbd)
        unit_pack_cost, est_per_pack_cost, act_per_pack_cost, est_total_cost, act_total_cost = pack_cost_info(
            pack_amount, pack_cost, est_material_cost, act_material_cost)
        pack_data = {
                'code': pack_batch['code'],
                'date': pack_batch['pack_start'],
                'name': pack_batch['product_package__name'],
                'pack_amount': pack_amount,
                'pack_unit': pack_unit,
                'per_pack_amount': per_pack_amount,
                'liter': round(liter, 4),
                'est_material_cost': est_material_cost,
                'act_material_cost': act_material_cost,
                'pack_cost': round(pack_cost, 4),
                'unit_pack_cost': unit_pack_cost,
                'est_total_cost': est_total_cost,
                'act_total_cost': act_total_cost,
                'est_per_pack_cost': est_per_pack_cost,
                'act_per_pack_cost': act_per_pack_cost,
                'pack_materials': material_batches_list,
            }
        pack_batches_data[pack_batch['code']] = {
            'est_per_pack_cost': est_per_pack_cost,
            'act_per_pack_cost': act_per_pack_cost,
        }
        batch_data['packs'].append(pack_data)
    # 组合
    group_batches = GroupBatch.objects.filter(pack_batch_item__pack_batch__product_batch=product_batch)
    if date_before:
        group_batches = group_batches.filter(group_start__lte=date_before)
    group_batches = group_batches.distinct().values(
        'pk',
        'code',
        'group_start',
        'package_group__name',
        'package_group__weight_net',
        'amount',
    ).order_by('pk')
    batch_data['groups'] = list()
    for group_batch in group_batches:
        group_pack_batches = PackBatchItem.objects.filter(
            group_batch_id=group_batch['pk']
        ).values(
            'id',
            'amount',
            'pack_batch__code',
            'pack_batch__product_package__name',
        )
        est_per_pack_item_cost = 0
        act_per_pack_item_cost = 0
        for pb in group_pack_batches:
            if group_batch['amount']:
                est_per_pack_item_cost += round(
                    pack_batches_data[pb['pack_batch__code']]['est_per_pack_cost'] * int(
                        pb['amount']
                    ) / int(group_batch['amount']),
                    4
                )
                act_per_pack_item_cost += round(
                    pack_batches_data[pb['pack_batch__code']]['act_per_pack_cost'] * int(
                        pb['amount']
                    ) / int(group_batch['amount']),
                    4
                )
        group_materials = MaterialOut.objects.filter(
            related_order__group_batch_id=group_batch['pk']
        ).annotate(
            material_batch_code=F('material_batch__code'),
            material_code=F('material_batch__material__code'),
            material_name=F('material_batch__material__name'),
            material_batch_unit_cost=F('material_batch__unit_cost'),
        ).values(
            'id',
            'amount',
            'material_batch_code',
            'material_code',
            'material_name',
            'material_batch_unit_cost',
        )
        group_pack_cost = 0
        per_group_pack_cost = 0
        group_materials_list = list()
        for gm in group_materials:
            gmd = dict()
            for k, v in gm.items():
                gmd[k] = v
            if gm['amount'] and gm['material_batch_unit_cost']:
                group_pack_cost += int(gm['amount']) * float(gm['material_batch_unit_cost'])
                gmd['material_batch_cost'] = float(gm['amount']) * float(gm['material_batch_unit_cost'])
            else:
                gmd['material_batch_cost'] = 0
            group_materials_list.append(gmd)
        if group_batch['amount']:
            per_group_pack_cost = round(group_pack_cost / group_batch['amount'], 4)
        est_per_group_cost = round(per_group_pack_cost + est_per_pack_item_cost, 4)
        act_per_group_cost = round(per_group_pack_cost + act_per_pack_item_cost, 4)
        group_data = {
            'pk': group_batch['pk'],
            'code': group_batch['code'],
            'date': group_batch['group_start'],
            'package_group_name': group_batch['package_group__name'],
            'amount': group_batch['amount'],
            'per_group_liter': group_batch['package_group__weight_net'],
            'group_pack_cost': round(group_pack_cost, 4),
            'per_group_pack_cost': round(per_group_pack_cost, 4),
            'est_per_pack_item_cost': est_per_pack_item_cost,
            'act_per_pack_item_cost': act_per_pack_item_cost,
            'est_per_group_cost': est_per_group_cost,
            'act_per_group_cost': act_per_group_cost,
            'group_materials': group_materials_list,
            'group_pack_batches': group_pack_batches
        }
        batch_data['groups'].append(group_data)
    batch_data['total_costs'] = total_production_cost + batch_data['total_pack_costs']
    if batch_data['product_state'] == '灌装完成':
        batch_data['total_left_value'] = 0
        if not batch_data['finish_date']:
            pack_batch = PackBatch.objects.filter(product_batch_id=product_batch_id).order_by('-pack_start').first()
            if pack_batch:
                if pack_batch.pack_start:
                    product_batch.finish_date = pack_batch.pack_start.strftime('%Y-%m-%d')
                    batch_data['finish_date'] = pack_batch.pack_start.strftime('%Y-%m-%d')
                    product_batch.save()
    else:
        batch_data['total_left_value'] = round(
            (batch_data['est_volume'] - batch_data['act_volume']) * est_material_cost_per_liter, 2)
    return batch_data


def export_batch_list(data):
    product_batches = data['product_batches']
    rows = list()
    for product_batch in product_batches:
        row_data = {}
        for k, v in product_batch.items():
            if k in ['packs']:
                p_d = list()
                for p in v:
                    p_d.append('{} {} 数量：{}，包材成本：{}元，均摊成本：{}元'.format(
                        p.get('date'),
                        p.get('name'),
                        p.get('pack_amount'),
                        p.get('pack_cost'),
                        p.get('est_per_pack_cost')
                    ))
                row_data['packs'] = '\n'.join(p_d)
            else:
                row_data[k] = v
        rows.append(row_data)
    headers = [
        '投料日期', '截止日期', '酒款', '酿造批次', '状态',
        '投量（L）', '预估产量（L）', '已灌装量（L）',
        '理论出酒率', '实际出酒率',
        '原料总价（元）', '总加工费（元）', '酿造总成本（元）',
        '理论酒液成本（元/L）', '灌装明细',
        '包材总价（元）', '酒液+包材总额（元）', '剩余酒液成本（元）'
    ]
    columns = [
        'start_date', 'finish_date', 'product_name', 'batch_code', 'product_state',
        'total_volume', 'est_volume', 'act_volume',
        'est_product_ratio', 'act_product_ratio',
        'total_material_costs', 'total_processing_costs', 'total_production_cost',
        'est_material_cost_per_liter', 'packs',
        'total_pack_costs', 'total_costs', 'total_left_value',
    ]
    return list_export_excel(
        data_list=rows,
        headers=headers,
        columns=columns,
        filename='media/material_{}.xls'.format(datetime.now().strftime('%Y%m%d%H%M%S'))
    )


def calculate_product_batch_material_list(date_before=None, q=''):
    mos = MaterialOut.objects.filter(
        related_order__product_batch__need_statistic=True
    )
    if q:
        mos = mos.filter(Q(related_order__product_batch__code__icontains=q) | Q(
            related_order__product_batch__product_name__name__icontains=q)).distinct()
    mos = mos.values(
        'related_order__code',
        'related_order__record_date',
        'related_order__product_batch__start_date',
        'related_order__product_batch__code',
        'related_order__product_batch__product_name__name',
        'related_order__product_batch__product_state',
        'related_order__product_batch__total_volume',
        'related_order__brew_batch__code',
        'related_order__pack_batch__code',
        'related_order__process_record__code',
        'related_order__process_record__process_type',
        'material_batch__code',
        'material__code',
        'material__name',
        'material_batch__unit',
        'amount',
        'material_batch__unit_cost',
    ).order_by(
        'related_order__product_batch__start_date',
        'related_order__product_batch__code'
    )
    if date_before:
        mos = mos.filter(related_order__record_date__lte=date_before)
    res = list()
    product_batch_data = dict()
    for mo in mos:
        product_state = None
        for state in choices_product_state:
            if mo['related_order__product_batch__product_state'] == state[0]:
                product_state = state[1]
        if mo['material_batch__unit_cost'] and mo['amount']:
            cost = float(mo['material_batch__unit_cost']) * float(mo['amount'])
        else:
            cost = 0
        if not mo['related_order__product_batch__code'] in product_batch_data:
            product_batch_data[mo['related_order__product_batch__code']] = {
                'product_batch_code': mo['related_order__product_batch__code'],
                'product_name': mo['related_order__product_batch__product_name__name'],
                'total_volume': mo['related_order__product_batch__total_volume'],
                'start_date': mo['related_order__product_batch__start_date'],
                'product_state': product_state,
                'material_list': list(),
                'rowspan': 1,
                'material_cost': 0
            }
        current_data = {
            'order_code': mo['related_order__code'],
            'record_date': mo['related_order__record_date'],
            'material_batch_code': mo['material_batch__code'],
            'material_code': mo['material__code'],
            'material_name': mo['material__name'],
            'unit': mo['material_batch__unit'],
            'amount': mo['amount'],
            'unit_cost': mo['material_batch__unit_cost'],
            'cost': cost
        }
        # 获取 stage 和 stage_code
        if mo['related_order__brew_batch__code']:
            current_data['stage_code'] = mo['related_order__brew_batch__code']
            current_data['stage'] = '酿造'
        elif mo['related_order__pack_batch__code']:
            current_data['stage_code'] = mo['related_order__pack_batch__code']
            current_data['stage'] = '灌装'
        else:
            for pt in choices_process_type:
                if mo['related_order__process_record__process_type'] == pt[0]:
                    current_data['stage'] = pt[1]
                    current_data['stage_code'] = mo['related_order__process_record__code']
        res.append(current_data)
        product_batch_data[mo['related_order__product_batch__code']]['material_list'].append(current_data)
        product_batch_data[mo['related_order__product_batch__code']]['rowspan'] += 1
        product_batch_data[mo['related_order__product_batch__code']]['material_cost'] += cost
    data = dict()
    data['res'] = res
    data['product_batch_data'] = product_batch_data
    return data


def export_material_list(date_before=None, q=''):
    data = calculate_product_batch_material_list(date_before=date_before, q=q)
    data_list = data['product_batch_data']
    rows = list()
    for batch_code, batch_data in data_list.items():
        for material in batch_data['material_list']:
            row_data = {}
            for k, v in batch_data.items():
                if k not in ['material_list']:
                    row_data[k] = v
            for k, v in material.items():
                row_data[k] = v
            rows.append(row_data)
    headers = [
        '酿造日期',
        '酿造批次',
        '酒款名称',
        '状态',
        '投量（L）',
        '成本合计(元)',
        '投料日期',
        '批次号',
        '投料形式',
        '物料代码',
        '物料名称',
        '物料批次',
        '单位',
        '使用数量',
        '单价（元）',
        '小计（元）'
    ]
    columns = [
        'start_date',
        'product_batch_code',
        'product_name',
        'product_state',
        'total_volume',
        'material_cost',
        'record_date',
        'stage_code',
        'stage',
        'material_code',
        'material_name',
        'material_batch_code',
        'unit',
        'amount',
        'unit_cost',
        'cost',
    ]
    return list_export_excel(
        data_list=rows,
        headers=headers,
        columns=columns,
        filename='media/material_{}.xls'.format(datetime.now().strftime('%Y%m%d%H%M%S'))
    )


def tank_state_list():
    data = dict()
    equipments = EquipmentCurrentProductBatch.objects.all().values(
        'equipment__code',
        'product_batch__id',
        'product_batch__code',
        'product_batch__product_name__name',
        'product_batch__start_date',
        'product_batch__total_volume',
        'product_batch__est_volume',
        'product_batch__act_volume',
        'tank_state',
        'datetime_start'
    ).order_by('equipment__code')
    equipment_list = list()
    for e in equipments:
        d = dict()
        for k, v in e.items():
            d[k] = v
        d['product_state'] = convert_choices_to_dict(choices_tank_state).get(d['tank_state'])
        if d['product_batch__start_date']:
            d['delta_days'] = (datetime.now().date() - e['product_batch__start_date']).days
        packs = PackBatch.objects.filter(product_batch_id=e['product_batch__id']).values(
            'code', 'product_name__name', 'product_package__name', 'product_package__code', 'pack_amount')
        d['packs'] = packs
        d['class'] = 'in-production'
        if d['product_batch__est_volume'] is not None and d['product_batch__act_volume'] is not None:
            d['volume_left'] = round((float(d['product_batch__est_volume']) - float(d['product_batch__act_volume'])), 2)
        if d['product_state'] in ['灌装完成', '待清洗', '已清洗', '待修']:
            d['class'] = 'finish'
        elif d['product_state'] == '可灌装':
            d['class'] = 'success'
        equipment_list.append(d)
    data['equipments'] = equipment_list
    return data


def convert_date_to_js_format(field_data, ferment_m, c_year, c_month, c_day, c_hour, c_min, field_name):
    if getattr(ferment_m, field_name):
        field_data.append(
            {'year': c_year, 'month': c_month, 'day': c_day, 'hour': c_hour, 'min': c_min,
             'data': str(getattr(ferment_m, field_name))})
    return field_data


def brew_ferment_m(ferment_ms=None):
    field_dict = {
        'plato': list(),
        'ph': list(),
        't_real': list(),
        't_set': list(),
    }
    if ferment_ms:
        for ferment_m in ferment_ms:
            c_time_string = ferment_m.recorded_at
            c_year = str(c_time_string)[0:4]
            c_month = int(str(c_time_string)[5:7]) - 1
            c_day = str(c_time_string)[8:10]
            c_hour = str(c_time_string)[11:13]
            c_min = str(c_time_string)[14:16]
            for k, v in field_dict.items():
                v = convert_date_to_js_format(v, ferment_m, c_year, c_month, c_day, c_hour, c_min, k)
                field_dict[k] = v
    return field_dict


def product_batch_detail(product_batch_id):
    try:
        product_batch = ProductBatch.objects.get(pk=product_batch_id)
    except ObjectDoesNotExist:
        return None
    data, material_batches = product_batch_basic_data(product_batch, date_before=None)
    material_batches = material_batches.annotate(
        date=F('related_order__record_date'),
        order_no=F('related_order__code'),
        batch_code=F('material_batch__code'),
        material_code=F('material_batch__material__code'),
        material_name=F('material_batch__material__name')
    ).values(
        'id',
        'order_no',
        'date',
        'batch_code',
        'material_code',
        'material_name',
    ).order_by('related_order__record_date', 'related_order__code')
    data['materials'] = material_batches
    # 灌装统计
    pack_batches = PackBatch.objects.filter(product_batch=product_batch)
    pack_batches = pack_batches.values(
        'pk',
        'code',
        'package_unit__display',
        'package_unit__amount',
        'package_unit__unit',
        'product_package__name',
        'pack_amount',
        'pack_start',
        'pack_end'
    ).distinct().order_by('pack_start')
    data['packs'] = list()
    for pack_batch in pack_batches:
        # 统计每个灌装批次的容量
        pack_amount, pack_unit, per_pack_amount, liter = pack_amount_info(pack_batch)
        pack_data = {
            'code': pack_batch['code'],
            'date': pack_batch['pack_start'],
            'name': pack_batch['product_package__name'],
            'pack_amount': pack_amount,
            'liter': round(liter, 4),
        }
        data['packs'].append(pack_data)
    ferment_ms = FermentMonitor.objects.filter(product_batch_id=product_batch_id)
    ferment_data = brew_ferment_m(ferment_ms)
    for k, v in ferment_data.items():
        data[k] = v
    process_records = ProcessRecord.objects.filter(product_batch_id=product_batch_id)
    data['process_records'] = process_records
    return data
